﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace EasyStudy.Logic
{
    public class EbbTaskDBMgr
    {
        public static readonly string DATETIME_FORMAT = "yy-MM-dd HH:mm";
        public static string DATABASE_FILENAME = "EbbDataBase.db";

        public static readonly string dbFileName;
        public static Properties.Settings settings;
        public static EasyStudy.SQLite.SQLiteDBHelper dbHelper;

        static EbbTaskDBMgr()
        {
            DATABASE_FILENAME = Configuration.GetInstance().DbFileName;
            dbFileName = System.AppDomain.CurrentDomain.BaseDirectory + DATABASE_FILENAME; 
            settings = new Properties.Settings();
            dbHelper = new SQLite.SQLiteDBHelper(dbFileName);
        }

        public static List<EbbinghausTask> GetTodayTasks()
        {
            List<EbbinghausTask> tasks = new List<EbbinghausTask>();

            string sqlTemplate = settings.SqlGetTodayTask;

            DateTime today = DateTime.Now;
            string queryFormat = today.ToString("yy-MM-dd");
            queryFormat += "%";

            string sql = string.Format(sqlTemplate, queryFormat);
            DataTable result = dbHelper.ExecuteDataTable(sql, null);

            foreach (DataRow row in result.Rows)
            {
                tasks.Add(DataRow2Task(row));
            }

            return tasks;
        }

        public static DataTable GetTodayTasksToDataTable()
        {
            string sqlTemplate = settings.SqlGetTodayTask4Show;

            DateTime today = DateTime.Now;
            string queryFormat = today.ToString("yy-MM-dd");
            queryFormat += "%";

            string sql = string.Format(sqlTemplate, queryFormat);
            DataTable result = dbHelper.ExecuteDataTable(sql, null);

            return result;
        }

        public static DataTable GetAllTasks()
        {
            DataTable dt = dbHelper.ExecuteDataTable(settings.SqlGetAllForShow, null);

            return dt;
        }

        public static DataTable GetUnfinishedTasksAWeekToDataTable()
        {
            DateTime[] times = Time.TimeMgr.GetPreviousAWeekTime();
            StringBuilder sBuilder = new StringBuilder();

            for (int i = 0; i < 7; ++i)
            {

                sBuilder.Append("NEXT_REMIND_TIME LIKE \"");
                sBuilder.Append(times[i].ToString("yy-MM-dd") + "%\"");
                if (i != 6)
                {
                    sBuilder.Append(" OR ");
                }
            }
            string sqlTemplate = settings.SqlGetPreviousUnFinishTask4Show;
            string sql = string.Format(sqlTemplate, sBuilder);

            DataTable res = dbHelper.ExecuteDataTable(sql, null);

            return res;
        }

        public static List<EbbinghausTask> GetUnfinishedTasksAWeek()
        {
            DateTime[] times = Time.TimeMgr.GetPreviousAWeekTime();
            StringBuilder sBuilder = new StringBuilder();

            for (int i = 0; i < 7; ++i)
            {

                sBuilder.Append("NEXT_REMIND_TIME LIKE \"");
                sBuilder.Append(times[i].ToString("yy-MM-dd") + "%\"");
                if (i != 6)
                {
                    sBuilder.Append(" OR ");
                }
            }
            string sqlTemplate = settings.SqlGetPreviousUnFinishTask;
            string sql = string.Format(sqlTemplate, sBuilder);

            DataTable res = dbHelper.ExecuteDataTable(sql, null);

            List<EbbinghausTask> tasks = new List<EbbinghausTask>();
            
            // 去掉已经更新的任务
            for (int i = 0; i < res.Rows.Count; ++i)
            {
                DateTime lastUpdateTime = DateTime.ParseExact(res.Rows[i]["LAST_UPDATE_TIME"].ToString(), 
                    DATETIME_FORMAT,
                    System.Globalization.CultureInfo.CurrentCulture);
                DateTime nextAlertTime = DateTime.ParseExact(res.Rows[i]["NEXT_REMIND_TIME"].ToString(),
                    DATETIME_FORMAT,
                    System.Globalization.CultureInfo.CurrentCulture);
                if (lastUpdateTime > nextAlertTime)
                {
                    res.Rows.RemoveAt(i);
                }
                else
                {
                    tasks.Add(DataRow2Task(res.Rows[i]));
                }
            }

            return tasks;
        }

        public static void FinishATask(string id)
        { 
            EbbinghausTask ebbTask = GetATask(id);
            FinishATask(ebbTask);
        }

        public static void FinishATask(EbbinghausTask task)
        {
            ++task.NextIndex;

            string sqlTemplate1 = settings.SqlFinishTaskOnce;
            string sql = string.Format(sqlTemplate1, task.NextIndex, task.ID);

            dbHelper.ExecuteNonQuery(sql, null);

            string sqlTemplate2 = settings.SqlUpdateTaskNextUpdate;
            string nextRemindTime = null;

            if (task.NextIndex < task.RemindTimes.Count)
            {
                nextRemindTime = FormatDateTime(task.RemindTimes[task.NextIndex]);
            }
            else
            {
                nextRemindTime = "finished";
            }

            sql = string.Format(sqlTemplate2,
                nextRemindTime, 
                FormatDateTime(task.NextTime),
                task.ID);
            dbHelper.ExecuteNonQuery(sql, null);
            tools.Sound.PlayFinish();
        }

        public static void AddAEbbTaskToDB(EbbinghausTask task)
        {
            string sqlTemplate = settings.SqlInsertTask;

            /*
            M       display one-digit month number          
            d       display one-digit day of the MONTH      
            h       display one-digit hour on 12-hour scale 
            HH      display two-digit hours on 24-hour scale
            mm      display two-digit minutes
            yy      display two-digit year                  
            */
            String createTime = FormatDateTime(task.CreateTime);
            String lastUpdateTime = FormatDateTime(task.UpdateTime);
            String nextRemindTime = FormatDateTime(task.NextTime);
            StringBuilder remindTimes = new StringBuilder();
            String type = task.Type.Trim();

            foreach(DateTime dt in task.RemindTimes) 
            {
                remindTimes.Append(FormatDateTime(dt));
                if (task.RemindTimes.IndexOf(dt) != task.RemindTimes.Count - 1)
                {
                    remindTimes.Append(";");
                }
            }
            String subject = task.Subject;
            String comment = task.Comment;

            String sql = String.Format(sqlTemplate, subject, 
                comment, 
                createTime,
                lastUpdateTime,
                nextRemindTime,
                remindTimes,
                0,
                type);

            dbHelper.ExecuteNonQuery(sql, null);
#if DEBUG
            Console.WriteLine("Insert success!");
#endif
        }

        public static String FormatDateTime(DateTime dt)
        {
            return dt.ToString(DATETIME_FORMAT);
        }

        public static void UpdateATask(String id, String newSubject, String newDescripion, String type)
        {
            string sqlTemplate = settings.SqlUpdateTask;
            string sql = string.Format(sqlTemplate, EbbinghausTask.ReplaceAllQuotas(newSubject), EbbinghausTask.ReplaceAllQuotas(newDescripion), type, id);

            dbHelper.ExecuteNonQuery(sql, null);
        }

        public static EbbinghausTask GetATask(String id)
        {
            string sqlTemplate = settings.SqlGetTaskCurrIndex;
            string sql = string.Format(sqlTemplate, id);

            DataTable dt = dbHelper.ExecuteDataTable(sql, null);

            return DataRow2Task(dt.Rows[0]);
        }

        public static DataTable ExecuteAQuery(string queryString)
        {
            string sqlTemplate = settings.SqlQueryTasks;
            string sql = string.Format(sqlTemplate, queryString);


            //System.Windows.Forms.MessageBox.Show(sql);
            DataTable dt = dbHelper.ExecuteDataTable(sql, null);

            return dt;
        }

        public static void DeleteATask(String id)
        {
            string sqlTemplate = settings.SqlDeleteTask;
            string sql = string.Format(sqlTemplate, id);

            dbHelper.ExecuteNonQuery(sql, null);
        }

        public static EbbinghausTask DataRow2Task(DataRow row)
        {
            EbbinghausTask task = new EbbinghausTask();
            task.ID = row["ID"].ToString();
            task.Subject = row["SUBJECT"].ToString();
            task.Comment = row["COMMENT"].ToString();
            task.Type = row["CATEGORY"].ToString();
            task.CreateTime = DateTime.ParseExact(row["CREATE_TIME"].ToString(), 
                DATETIME_FORMAT, 
                System.Globalization.CultureInfo.CurrentCulture);
            task.UpdateTime = DateTime.ParseExact(row["LAST_UPDATE_TIME"].ToString(),
                DATETIME_FORMAT,
                System.Globalization.CultureInfo.CurrentCulture);
            int nextIndex = 0;
            if (int.TryParse(row["NEXT_INDEX"].ToString(), out nextIndex))
            {
                task.NextIndex = nextIndex;
            }
            task.NextTime = DateTime.ParseExact(row["NEXT_REMIND_TIME"].ToString(),
                DATETIME_FORMAT,
                System.Globalization.CultureInfo.CurrentCulture);

            string strRemindTimeList = row["REMIND_TIME_LIST"].ToString();
            string[] remindTimeList = strRemindTimeList.Split(";".ToCharArray(), 
                StringSplitOptions.RemoveEmptyEntries);
            
            foreach (string remindTime in remindTimeList)
            {
                task.RemindTimes.Add(DateTime.ParseExact(remindTime,
                    DATETIME_FORMAT,
                    System.Globalization.CultureInfo.CurrentCulture));
            }

            return task;
        }

        /// <summary>
        /// 获取所有任务类型列表
        /// </summary>
        /// <returns></returns>
        public static List<string> getAllCategories()
        {
            string sql = settings.SqlQueryGetAllCategory;

            DateTime now = DateTime.Now;
            now = now.AddDays(-60);
            String dateTimeStr = FormatDateTime(now);
            sql = String.Format(sql, dateTimeStr);


            DataTable dt = dbHelper.ExecuteDataTable(sql, null);
            List<string> categories = new List<string>();
            
            if(dt == null) { return categories;}

            foreach(DataRow dr in dt.Rows) {
                if (dr[0].ToString().Trim() != String.Empty)
                {
                    categories.Add(dr[0].ToString());
                }
            }

            return categories;
        }
    }
}
